using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;

using RecruitmentSystem.Common;

namespace RecruitmentSystem.DataAccess
{
    public class NewsAccessor
    {
        public bool AddNews(News news)
        {
            const string SpName = "SpAddNews";

            try
            {
                object[] parameters = new object[]
                {
                    news.Title,
                    news.CreateTime,
                    news.CategoryId,
                    news.Content,
                    news.Author,
                    news.Source,
                    news.IsPublish,
                    news.Clicks
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in add news", ex);
            }

            return true;
        }

        public News GetNewsById(int id)
        {
            News news = new News();
            const string SpName = "SpGetNewsById";
            SqlDataReader reader = null;
            try
            {
                object[] parameters = new object[]
                {
                    id
                };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString,
                    SpName, parameters);

                while (reader.Read())
                {
                    news.Id = reader.GetInt32(0);
                    news.Title = !reader.IsDBNull(1) ? reader.GetString(1) : null;
                    news.CreateTime = reader.GetDateTime(2);
                    news.CategoryId = reader.GetInt32(3);
                    news.Content = !reader.IsDBNull(4) ? reader.GetString(4) : null;
                    news.Author = !reader.IsDBNull(5) ? reader.GetString(5) : null;
                    news.Source = !reader.IsDBNull(6) ? reader.GetString(6) : null;
                    news.IsPublish = reader.GetBoolean(7);
                    news.Clicks = reader.GetInt32(8);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get news by id", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }
            return news;
        }

        public List<News> GetNewsByCategoryId(int? id, int pageSize, int pageCurrent)
        {
            List<News> allNews = new List<News>();
            const string SpName = "SpGetNewsByCategoryId";
            SqlDataReader reader=null;
            try
            {
                object[] parameters = new object[]
                {
                    id,
                    pageSize,
                    pageCurrent
                };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString,
                    SpName, parameters);

                while (reader.Read())
                {
                    News news = new News();

                    news.Id = reader.GetInt32(0);
                    news.Title = !reader.IsDBNull(1) ? reader.GetString(1) : null;
                    news.CreateTime = reader.GetDateTime(2);
                    news.CategoryId = reader.GetInt32(3);
                    news.Content = !reader.IsDBNull(4) ? reader.GetString(4) : null;
                    news.Author = !reader.IsDBNull(5) ? reader.GetString(5) : null;
                    news.Source = !reader.IsDBNull(6) ? reader.GetString(6) : null;
                    news.IsPublish = reader.GetBoolean(7);
                    news.Clicks = reader.GetInt32(8);

                    allNews.Add(news);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get all news by id", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }
            return allNews;
        }

        public int GetNewsCountByCategoryId(int? categoryId)
        {
            int count = 0;
            const string SpName = "SpGetNewsCountByCategoryId";

            try
            {
                object[] parameters = new object[]
                {
                    categoryId
                };

                object o = SqlHelper.ExecuteScalar(Constants.ConnectionString, SpName, parameters);
                if (o != null)
                {
                    count = Convert.ToInt32(o);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get all news count", ex);
            }

            return count;
        }

        public bool UpdateNewsById(News news)
        {
            const string SpName = "SpUpdateNewsById";

            try
            {
                object[] parameters = new object[]
                {
                    news.Id,
                    news.Title,
                    news.CreateTime,
                    news.CategoryId,
                    news.Content,
                    news.Author,
                    news.Source,
                    news.IsPublish,
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in update news by id", ex);
            }

            return true;
        }

        public bool DeleteNewsById(int id)
        {
            const string SpName = "SpDeleteNewsById";

            try
            {
                object[] parameters = new object[]
                {
                    id
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in update news by id", ex);
            }

            return true;
        }

        public List<Category> GetAllCategory(int? level)
        {
            List<Category> allCategory = new List<Category>();
            const string SpName = "SpGetAllCategory";
            SqlDataReader reader = null;
            try
            {
                object[] parameters = new object[]
                {
                    level
                };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString,
                    SpName, parameters);

                while (reader.Read())
                {
                    Category category = new Category();

                    category.Id = reader.GetInt32(0);
                    category.Name = !reader.IsDBNull(1) ? reader.GetString(1) : null;
                    category.ParentId = reader.GetInt32(2);
                    category.Level = reader.GetInt32(3);

                    allCategory.Add(category);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get all category", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }
            return allCategory;
        }

        public bool AddCategory(Category category)
        {
            const string SpName = "SpAddCategory";

            try
            {
                object[] parameters = new object[]
                {
                    category.Name,
                    category.ParentId,
                    category.Level
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in add category", ex);
            }

            return true;
        }

        public bool UpdateCategoryById(Category category)
        {
            const string SpName = "SpUpdateCategoryById";

            try
            {
                object[] parameters = new object[]
                {
                    category.Id,
                    category.Name,
                    category.ParentId,
                    category.Level
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in update category by id", ex);
            }

            return true;
        }

        public Category GetCategoryById(int id)
        {
            Category category = new Category();
            const string SpName = "SpGetCategoryById";
            SqlDataReader reader = null;
            try
            {
                object[] parameters = new object[]
                {
                    id
                };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString,
                    SpName, parameters);

                while (reader.Read())
                {
                    category.Id = reader.GetInt32(0);
                    category.Name = !reader.IsDBNull(1) ? reader.GetString(1) : null;
                    category.ParentId = reader.GetInt32(2);
                    category.Level = reader.GetInt32(3);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get category by id", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

            }
            return category;
        }
    }
}
